﻿using System.Collections.Generic;

namespace Netnr.DataKit.DK
{
    /// <summary>
    /// Oracle
    /// </summary>
    public class DataKitOracle : IDataKit
    {
        /// <summary>
        /// 获取所有表信息的SQL脚本
        /// </summary>
        public string GetTableSQL = @"  
                                        SELECT
                                          A.table_name AS TableName,
                                          B.comments AS TableComment
                                        FROM
                                          user_tables A,
                                          user_tab_comments B
                                        WHERE
                                          A.table_name = B.table_name
                                        ORDER BY A.table_name
                                    ";

        /// <summary>
        /// 获取所有列信息的SQL脚本
        /// </summary>
        public string GetColumnSQL = @"
                                        SELECT
                                            A.TABLE_NAME AS TableName,
                                            B.COMMENTS AS TableComment,
                                            C.COLUMN_NAME AS FieldName,
                                            C.DATA_TYPE || '(' || CASE
                                            WHEN C.CHARACTER_SET_NAME = 'NCHAR_CS' THEN C.DATA_LENGTH / 2
                                            ELSE C.DATA_LENGTH
                                            END || ')' AS DataTypeLength,
                                            C.DATA_TYPE AS DataType,
                                            CASE
                                            WHEN C.CHARACTER_SET_NAME = 'NCHAR_CS' THEN C.DATA_LENGTH / 2
                                            ELSE C.DATA_LENGTH
                                            END AS DataLength,
                                            C.DATA_SCALE AS DataScale,
                                            C.COLUMN_ID AS FieldOrder,
                                            DECODE(PK.COLUMN_NAME, C.COLUMN_NAME, 'YES', '') AS PrimaryKey,
                                            DECODE(C.NULLABLE, 'N', 'YES', '') AS NotNull,
                                            C.DATA_DEFAULT AS DefaultValue,
                                            D.COMMENTS AS FieldComment
                                        FROM
                                            USER_TABLES A
                                            LEFT JOIN USER_TAB_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME
                                            LEFT JOIN ALL_TAB_COLUMNS C ON A.TABLE_NAME = C.TABLE_NAME
                                            LEFT JOIN USER_COL_COMMENTS D ON A.TABLE_NAME = D.TABLE_NAME
                                            AND C.COLUMN_NAME = D.COLUMN_NAME
                                            LEFT JOIN (
                                            SELECT
                                                E.TABLE_NAME,
                                                F.COLUMN_NAME
                                            FROM
                                                ALL_CONSTRAINTS E
                                                LEFT JOIN USER_CONS_COLUMNS F ON E.TABLE_NAME = F.TABLE_NAME
                                                AND E.CONSTRAINT_NAME = F.CONSTRAINT_NAME
                                            WHERE
                                                E.CONSTRAINT_TYPE = 'P'
                                            ) PK ON PK.TABLE_NAME = A.TABLE_NAME
                                            AND C.COLUMN_NAME = PK.COLUMN_NAME
                                        WHERE
                                            1 = 1 @SqlWhere@
                                        ORDER BY
                                            A.TABLE_NAME,
                                            C.COLUMN_ID
                                    ";

        /// <summary>
        /// 设置表注释的SQL脚本
        /// </summary>
        public string SetTableCommentSQL = "comment on table \"@DataTableName@\" is '@Comment@'";

        /// <summary>
        /// 设置列注释的SQL脚本
        /// </summary>
        public string SetColumnCommentSQL = "comment on column \"@DataTableName@\".\"@DataColumnName@\" is '@Comment@';";

        /// <summary>
        /// 获取所有列
        /// </summary>
        /// <param name="listTableName">表名</param>
        /// <returns></returns>
        public List<Model.DkTableColumn> GetColumn(List<string> listTableName = null)
        {
            var whereSql = string.Empty;

            if (listTableName?.Count > 0)
            {
                listTableName.ForEach(x => x = x.Replace("'", ""));

                whereSql = "AND A.TABLE_NAME IN ('" + string.Join("','", listTableName) + "')";
            }

            var sql = GetColumnSQL.Replace("@SqlWhere@", whereSql);
            var ds = new DB.OracleDB().Query(sql);

            var list = ds.Tables[0].ToModel<Model.DkTableColumn>();

            return list;
        }

        /// <summary>
        /// 获取所有表
        /// </summary>
        /// <returns></returns>
        public List<Model.DkTableName> GetTable()
        {
            var ds = new DB.OracleDB().Query(GetTableSQL);

            var list = ds.Tables[0].ToModel<Model.DkTableName>();

            return list;
        }

        /// <summary>
        /// 设置表注释
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="TableComment">表注释</param>
        /// <returns></returns>
        public bool SetTableComment(string TableName, string TableComment)
        {
            var sql = SetTableCommentSQL.Replace("@DataTableName@", TableName.Replace("\"", "")).Replace("@Comment@", TableComment.Replace("'", "''"));
            new DB.OracleDB().ExecuteSql(sql);
            return true;
        }

        /// <summary>
        /// 设置列注释
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="FieldName">列名</param>
        /// <param name="FieldComment">列注释</param>
        /// <returns></returns>
        public bool SetColumnComment(string TableName, string FieldName, string FieldComment)
        {
            var sql = SetColumnCommentSQL.Replace("@DataTableName@", TableName.Replace("\"", "")).Replace("@DataColumnName@", FieldName.Replace("\"", "")).Replace("@Comment@", FieldComment.Replace("'", "''"));
            new DB.OracleDB().ExecuteSql(sql);
            return true;
        }
    }
}
